const sql = require('mssql');


/**
 * 
 * 拣货复核系统
 */
/**
 * 
 * 复核获得订单信息
 */
const getPickGoodReviewOrderInfor = async (ctx, next) => {
    const body = ctx.request.body;
    // let totalSql = `SELECT  COUNT(t1.id) AS TOTAL FROM (SELECT id,carid FROM YHDD WHERE id <>ALL (SELECT id FROM YHDD WHERE JHZT = 'F'  OR SFYC = 'T' OR FHZT = 'T') `;
    // let startSql = `SELECT * FROM (SELECT  ROW_NUMBER() OVER(ORDER BY t1.id) AS px,t1.id,X.RQ,X.ID_DHD,X.DWMC,B.DH,t1.carid,t1.MC FROM (SELECT Y.id,Y.carid,B.MC FROM YHDD Y LEFT JOIN BM_YG B ON Y.userid = B.BM WHERE Y.id <>ALL (SELECT id FROM YHDD WHERE JHZT = 'F'  OR SFYC = 'T' OR FHZT = 'T')  `;
    let totalSql = `SELECT  COUNT(t1.id) AS TOTAL FROM (SELECT Y.id,Y.carid,Y.userid FROM YHDD	Y LEFT JOIN (SELECT id FROM YHDD WHERE JHZT = 'F'  OR SFYC = 'T' OR FHZT = 'T' )N ON Y.id = N.id WHERE N.ID IS NULL `;
    let startSql = `SELECT * FROM (SELECT  ROW_NUMBER() OVER(ORDER BY X.ZSL) AS px,t1.id,X.RQ,X.ID_DHD,X.DWMC,X.JSFSBM,B.DH,t1.carid,t1.MC FROM (SELECT Y.id,Y.carid,B.MC FROM YHDD Y LEFT JOIN BM_YG B ON Y.userid = B.BM LEFT JOIN (SELECT id FROM YHDD WHERE JHZT = 'F'  OR SFYC = 'T' OR FHZT = 'T' )N ON Y.id = N.id WHERE N.ID IS NULL  `;


    totalSql += ` GROUP BY Y.id,Y.carid,Y.userid) AS t1 LEFT JOIN XSD_ZB X ON t1.id =X.ID LEFT JOIN BM_WLDW B ON X.DWBM = B.BM `;
    startSql += `GROUP BY Y.id,Y.carid,B.MC) AS t1 LEFT JOIN XSD_ZB X ON t1.id =X.ID LEFT JOIN BM_WLDW B ON X.DWBM = B.BM `;

    let allOrderCount = 0;
    if (body.searchValList.carid) {
        // 如果通过拣货篮搜索就展示所有订单的总数量
        let allOrderCountResult = await sql.query(`SELECT SUM(ZSL) AS ZSL FROM XSD_ZB WHERE ID IN (SELECT Y.id FROM YHDD	
            Y LEFT JOIN (SELECT id FROM YHDD WHERE JHZT = 'F'  OR SFYC = 'T' OR FHZT = 'T' )N ON Y.id = N.id WHERE N.ID IS NULL 
            AND Y.carid = '${body.searchValList.carid}' GROUP BY Y.id,Y.carid,Y.userid)`);
        allOrderCount = allOrderCountResult.recordset[0].ZSL;
       
        startSql += ` WHERE t1.carid = '${body.searchValList.carid}'`;
        totalSql += ` WHERE t1.carid = '${body.searchValList.carid}'`;
        allOrderCountSql = `SELECT SUM(ZSL) AS ZSL FROM XSD_ZB WHERE ID IN (SELECT Y.id FROM YHDD	Y LEFT JOIN 
            (SELECT id FROM YHDD WHERE JHZT = 'F'  OR SFYC = 'T' OR FHZT = 'T' )N ON Y.id = N.id WHERE N.ID IS NULL GROUP BY Y.id,Y.carid,Y.userid)`;
    }
    if (body.searchValList.customer) {
        let whereCount = startSql.split('WHERE').length - 1;
        startSql += whereCount <= 2 ? 'WHERE' : 'AND';
        startSql += ` (X.DWMC = '${body.searchValList.customer}' OR B.DH = '${body.searchValList.customer}')`;
        totalSql += whereCount <= 2 ? 'WHERE' : 'AND';
        totalSql += ` (X.DWMC = '${body.searchValList.customer}' OR B.DH = '${body.searchValList.customer}')`;
    }



    if (body.searchValList.jsfsmc) {
        let whereCount = startSql.split('WHERE').length - 1;
        startSql += whereCount <= 2 ? 'WHERE' : 'AND';
        startSql += ` X.JSFSMC = '${body.searchValList.jsfsmc}'`;
        totalSql += whereCount <= 2 ? 'WHERE' : 'AND';
        totalSql += ` X.JSFSMC = '${body.searchValList.jsfsmc}'`;
    }
    if (body.searchValList.yptm) {
        let whereCount = startSql.split('WHERE').length - 1;
        startSql += whereCount <= 2 ? 'WHERE' : 'AND';
        startSql += ` t1.id IN (SELECT V.ID FROM XSD_MX V LEFT JOIN BM_YP Q ON V.YPBM =  Q.BM   WHERE (Q.TM LIKE '%${body.searchValList.yptm}%' OR Q.JP LIKE '%${body.searchValList.yptm}%' OR Q.MC LIKE '%${body.searchValList.yptm}%'))`;
        totalSql += whereCount <= 2 ? 'WHERE' : 'AND';
        totalSql += ` t1.id IN (SELECT V.ID FROM XSD_MX V LEFT JOIN BM_YP Q ON V.YPBM =  Q.BM   WHERE (Q.TM LIKE '%${body.searchValList.yptm}%' OR Q.JP LIKE '%${body.searchValList.yptm}%' OR Q.MC LIKE '%${body.searchValList.yptm}%'))`;
    }
   
    startSql += `) AS t2 WHERE t2.px between ${(body.pageNum - 1) * body.pageSize + 1} and ${body.pageNum * body.pageSize} `

    let result = await sql.query(startSql);
    let total = await sql.query(totalSql);
    ctx.body = { total: total.recordset[0].TOTAL, result: result.recordset,allOrderCount }
}
/**
 * 选择拣货订单展示对应订单下商品的信息
 */


const getPickGoodReviewShopInfor = async (ctx, next) => {
    const body = ctx.request.body;
    let startSql = `SELECT ROW_NUMBER() OVER(ORDER BY Y.YPBM) AS px,Y.YPBM,t1.TM AS YPTM,t1.YPMC,t1.YPGG,t1.JLDW, t1.SCCJ,t1.YXQZ,Y.id AS ID,t1.JP,t1.JXMC,t1.CCTJ,t1.DJ,t1.JE,t1.HWBH,t1.YJS,t1.DWMC,t1.DH,t1.DZ,Y.SCPH,t1.SL FROM YHDD Y LEFT JOIN (SELECT X.YPBM,P.TM,X.YPMC,X.YPGG,X.JLDW, X.SCCJ,X.YXQZ,X.SCPH,X.ID,P.JP,P.JXMC,P.CCTJ,X.DJ,X.JE,X.HWBH,Z.YJS,Z.DWMC,W.DH,W.DZ,SUM(X.SL) AS SL FROM XSD_MX X LEFT JOIN BM_YP P ON X.YPBM=P.BM LEFT JOIN XSD_ZB Z  ON X.ID = Z.ID LEFT JOIN BM_WLDW W ON Z.DWBM = W.BM GROUP BY X.YPBM,P.TM,X.YPMC,X.YPGG,X.JLDW, X.SCCJ,X.YXQZ,X.SCPH,X.ID,P.JP,P.JXMC,P.CCTJ,X.DJ,X.JE,X.HWBH,Z.YJS,Z.DWMC,W.DH,W.DZ) AS t1 ON Y.id = t1.ID AND Y.YPBM = t1.YPBM AND Y.SCPH = t1.SCPH WHERE Y.id='${body.chooseVal}' AND Y.JHZT = 'T'`;
    let result = await sql.query(startSql);
    ctx.body = { result: result.recordset }
}
/**
 * 
 *格式化日期
 */
const dateFomat = (fmt, date) => {
    var o = {
        "M+": date.getMonth() + 1,                 //月份   
        "d+": date.getDate(),                    //日   
        "h+": date.getHours(),                   //小时   
        "m+": date.getMinutes(),                 //分   
        "s+": date.getSeconds(),                 //秒   
        "q+": Math.floor((date.getMonth() + 3) / 3), //季度   
        "S": date.getMilliseconds()             //毫秒   
    };
    if (/(y+)/.test(fmt))
        fmt = fmt.replace(RegExp.$1, (date.getFullYear() + "").substr(4 - RegExp.$1.length));
    for (var k in o)
        if (new RegExp("(" + k + ")").test(fmt))
            fmt = fmt.replace(RegExp.$1, (RegExp.$1.length == 1) ? (o[k]) : (("00" + o[k]).substr(("" + o[k]).length)));
    return fmt + '.000';
}
/**
 * 改变复核完的订单状态
 */
const changePickGoodReviewOrderStatus = async (ctx, next) => {
    const body = ctx.request.body;
    // 跟新库存并改变出库状态
    // let getAllShopSql = `SELECT * FROM XSD_MX WHERE ID = '${body.orderId}'`;
    // let allShopResult = await sql.query(getAllShopSql);
    // let allShop = allShopResult.recordset;
    // allShop.forEach(async item => {
    //     let stockCountResult = await sql.query(`SELECT KCSL FROM KC_KCDTB WHERE BMBM = '07' AND YPBM = '${item.YPBM}' AND SCPH = '${item.SCPH}' AND CKBM = '${item.CKBM}'`);
    //     let stockCount = stockCountResult.recordset[0].KCSL;
    //     let updateStockSql = `UPDATE KC_KCDTB SET KCSL = ${stockCount -
    //         item.SL},KYSL =${stockCount - item.SL},KCJE=${(stockCount - item.SL) *
    //         item.CBJJ} WHERE BMBM = '07' AND YPBM = '${item.YPBM}' AND SCPH = '${
    //         item.SCPH
    //         }' AND CKBM = '${item.CKBM}'`;
    //         await sql.query(updateStockSql);
    // });
    // await sql.query(`UPDATE XSD_ZB SET SH = 'T' WHERE ID = '${body.orderId}'`);
    let updataSql = `UPDATE YHDD SET FHZT = 'T',FHRQ = '${dateFomat("yyyy-MM-dd hh:mm:ss", new Date())}' WHERE id ='${body.orderId}'`;
    let result = await sql.query(updataSql);
    if (result.rowsAffected[0] > 0) {
        ctx.body = { success: true }
    } else {
        ctx.body = { success: false }
    }
}
/**
 * 标注有差异的订单
 */
const markReviewDiffOrder = async (ctx, next) => {
    const body = ctx.request.body;
    // let isExistSql = `SELECT COUNT(ID) AS count FROM JHCY WHERE ID = '${body.shopInfo.ID}'`;
    // let isExist = await sql.query(isExistSql);
    // if(isExist.recordset[0].count==0){
    let insertSql = `INSERT INTO JHCY VALUES('${body.shopInfo.ID}','${body.person}','${body.reason}','${body.shopInfo.YPBM}','${body.shopInfo.SCPH}','${dateFomat("yyyy-MM-dd hh:mm:ss", new Date())}','')`;
    let insertResult = await sql.query(insertSql);
    // }
    // let updateSql = `UPDATE YHDD SET SFYC = 'T' WHERE userid = '${body.userId}' AND id = '${body.orderId}' AND YPBM = '${body.ypbm}' AND SCPH = '${body.scph}'`;

    // let result = await sql.query(updateSql);
    // let updateSql = `UPDATE YHDD SET JHZT = 'F',JHRQ = '${dateFomat("yyyy-MM-dd hh:mm:ss",new Date())}' WHERE id ='${body.shopInfo.ID}' AND YPBM ='${body.shopInfo.YPBM}' AND SCPH ='${body.shopInfo.SCPH}' `;
    let updateSql = `UPDATE YHDD SET SFYC = 'T' WHERE id ='${body.shopInfo.ID}' AND YPBM ='${body.shopInfo.YPBM}' AND SCPH ='${body.shopInfo.SCPH}' `;
    let result = await sql.query(updateSql);
    // let updataSql = `UPDATE YHDD SET FHZT = 'T',FHRQ = '${dateFomat("yyyy-MM-dd hh:mm:ss",new Date())}' WHERE id ='${body.orderId}'`;
    // let result = await sql.query(updataSql);
    if (result.rowsAffected[0] > 0 && insertResult.rowsAffected[0] > 0) {
        ctx.body = { success: true }
    } else {
        ctx.body = { success: false }
    }
}





module.exports = {
    'POST /getPickGoodReviewOrderInfor': getPickGoodReviewOrderInfor,
    'POST /getPickGoodReviewShopInfor': getPickGoodReviewShopInfor,
    'POST /markReviewDiffOrder': markReviewDiffOrder,
    'POST /changePickGoodReviewOrderStatus': changePickGoodReviewOrderStatus

    // 'GET /getPickGoodsOrder': getPickGoodsOrder,

}